DROP DATABASE IF EXISTS oefendb;

CREATE DATABASE IF NOT EXISTS oefendb;

USE oefendb;

CREATE TABLE IF NOT EXISTS ARTIKEL
(
    ANO VARCHAR(2) NOT NULL,
    ANAAM VARCHAR(10),
    KLEUR VARCHAR(10),
    GEW INTEGER,
    PLAATS VARCHAR(10),
    CONSTRAINT PK_ANO PRIMARY KEY (ANO));

CREATE TABLE IF NOT EXISTS LEVERANCIER
(
    LNO VARCHAR(2) NOT NULL,
	LNAAM VARCHAR(10),
    STATUS INTEGER,
    WNPL VARCHAR(10),
    CONSTRAINT PK_LNO PRIMARY KEY (LNO));

CREATE TABLE IF NOT EXISTS PRODUCT
(
    PNO VARCHAR(2) NOT NULL,
    PNAAM VARCHAR(10),
    STAD VARCHAR(10),
    CONSTRAINT PK_PNO PRIMARY KEY (PNO));

CREATE TABLE IF NOT EXISTS LAP
(
    LNO VARCHAR(2) NOT NULL,
    ANO VARCHAR(2) NOT NULL,
    PNO VARCHAR(2) NOT NULL,
    HOEV int,
    CONSTRAINT FK_LNO FOREIGN KEY (LNO) REFERENCES leverancier(LNO),
    CONSTRAINT FK_ANO FOREIGN KEY (ANO) REFERENCES artikel(ANO),
    CONSTRAINT FK_PNO FOREIGN KEY (PNO) REFERENCES product(PNO)
); 

INSERT INTO `artikel` VALUES ('A1', 'Moer', 'Rood', '12', 'Amsterdam');
INSERT INTO `artikel` VALUES ('A2', 'Bout', 'Groen', '17', 'Oranjestad');
INSERT INTO `artikel` VALUES ('A3', 'Schroef', 'Blauw', '17', 'Bogota');
INSERT INTO `artikel` VALUES ('A4', 'Schroef', 'Rood', '14', 'Amsterdam');
INSERT INTO `artikel` VALUES ('A5', 'Nok', 'Blauw', '12', 'Oranjestad');
INSERT INTO `artikel` VALUES ('A6', 'Kamrad', 'Rood', '19', 'Amsterdam');

INSERT INTO `leverancier` VALUES ('L1', 'Ellis', '20', 'Amsterdam');
INSERT INTO `leverancier` VALUES ('L2', 'Tromp', '10', 'Oranjestad');
INSERT INTO `leverancier` VALUES ('L3', 'Arends', '30', 'Oranjestad');
INSERT INTO `leverancier` VALUES ('L4', 'Fernandez', '20', 'Amsterdam');
INSERT INTO `leverancier` VALUES ('L5', 'Kock', '30', 'Willemstad');

INSERT INTO `product` VALUES ('P1', 'Scherm', 'Oranjestad');
INSERT INTO `product` VALUES ('P2', 'Ponser', 'Bogota');
INSERT INTO `product` VALUES ('P3', 'Lezer', 'Willemstad');
INSERT INTO `product` VALUES ('P4', 'Console', 'Willemstad');
INSERT INTO `product` VALUES ('P5', 'Schijf', 'Amsterdam');
INSERT INTO `product` VALUES ('P6', 'Terminal', 'Caracas');
INSERT INTO `product` VALUES ('P7', 'Band', 'Amsterdam');  

INSERT INTO `lap` VALUES ('L1', 'A1', 'P1', '200');
INSERT INTO `lap` VALUES ('L1', 'A1', 'P4', '700');
INSERT INTO `lap` VALUES ('L2', 'A3', 'P1', '400');
INSERT INTO `lap` VALUES ('L2', 'A3', 'P2', '200');
INSERT INTO `lap` VALUES ('L2', 'A3', 'P3', '200');
INSERT INTO `lap` VALUES ('L2', 'A3', 'P4', '500');
INSERT INTO `lap` VALUES ('L2', 'A3', 'P5', '600');
INSERT INTO `lap` VALUES ('L2', 'A3', 'P6', '400');
INSERT INTO `lap` VALUES ('L2', 'A3', 'P7', '800');
INSERT INTO `lap` VALUES ('L2', 'A5', 'P2', '100');
INSERT INTO `lap` VALUES ('L3', 'A3', 'P1', '200');
INSERT INTO `lap` VALUES ('L3', 'A4', 'P2', '500');
INSERT INTO `lap` VALUES ('L4', 'A6', 'P3', '300');
INSERT INTO `lap` VALUES ('L4', 'A6', 'P7', '300');
INSERT INTO `lap` VALUES ('L5', 'A1', 'P4', '100');
INSERT INTO `lap` VALUES ('L5', 'A2', 'P2', '200');
INSERT INTO `lap` VALUES ('L5', 'A2', 'P4', '100');
INSERT INTO `lap` VALUES ('L5', 'A3', 'P4', '200');
INSERT INTO `lap` VALUES ('L5', 'A4', 'P4', '800');
INSERT INTO `lap` VALUES ('L5', 'A5', 'P4', '400');
INSERT INTO `lap` VALUES ('L5', 'A5', 'P5', '500');
INSERT INTO `lap` VALUES ('L5', 'A5', 'P7', '100');
INSERT INTO `lap` VALUES ('L5', 'A6', 'P2', '200');
INSERT INTO `lap` VALUES ('L5', 'A6', 'P4', '500');

